package com.netcracker.studportal.server.dao.impl;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.*;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.json.simple.JSONArray;

import com.netcracker.studportal.server.dao.StudentDAO;
import com.netcracker.studportal.server.model.Student;
import com.netcracker.studportal.server.util.HibernateUtil;

public class StudentDAOImpl implements StudentDAO{
	private static final Logger LOG = Logger.getLogger(StudentDAOImpl.class);
	
	@Override
	public void Add(Student stud) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.save(stud);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public void Update(Student stud) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.update(stud);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public Student getById(Long id) throws Exception {
		Session session = null;
		Student stud = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			stud = (Student) session.load(Student.class, id);
			Hibernate.initialize(stud.getExamWithResult());
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}

		return stud;
	}

	@Override
	public void Delete(Student stud) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.delete(stud);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public Collection<Student> getAll() throws Exception {
		Session session = null;
		List<Student> stud = new ArrayList<Student>();
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			stud = session.createCriteria(Student.class).list();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
		return stud;
	}
	
	@Override
	public Collection<Student> getGetStudentsByExam(Long exam_id) throws Exception {
		Session session = null;
		Collection<Student> stud= null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			stud = session
					.createSQLQuery(
							"select s.* from students s,exam_results e where s.student_id=e.student_id and e.exam_id="
									+ exam_id).addEntity(Student.class).list();

		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
		return stud;	
	}
	
	@Override
	public List<Map<String,String>> getGroupsByFaculty(Long faculty_id) throws Exception {
		Session session = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			
			SQLQuery q = session.createSQLQuery("SELECT distinct group_num, course FROM students WHERE faculty_id="+
					faculty_id);
			
			List<Object []> list = q.list();
			List<Map<String,String>> map_list = new ArrayList<Map<String,String>>();
			for(Object[] obj : list){
				Map<String,String> m = new HashMap<String,String>();
				m.put("group_num", obj[0].toString());
				m.put("course", obj[1].toString());
				//m.put(obj[0].toString(), obj[1].toString());
				map_list.add(m);
			}
			
			return map_list;
			
		}catch(Exception e){
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		}finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public void AddExamToStudentGroup(Long exam_id, Integer course,
			Integer group_num,Long faculty_id) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			String insertQuery =
			"INSERT INTO exam_results (exam_id,student_id)"
				+ " (select "+exam_id+",student_id from students "
						+ " where course="+course+" AND group_num="+group_num
						+" AND faculty_id="+faculty_id+")";
			
			session.createSQLQuery(insertQuery).executeUpdate();
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}
	
	
	/**
	 * Realization of singleton.
	 */
	private StudentDAOImpl(){}
	private static StudentDAO instance;
	public static StudentDAO getInstance() {
		if (instance == null) {
			instance = new StudentDAOImpl();
		}
		return instance;
	}

}
